﻿/* 
 * Since 2008 - 2013
 */

package com.hh.bss.oa.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.oa.dao.*;
import com.hh.bss.oa.entity.*;
import com.hh.bss.oa.service.*;
import com.hh.bss.oa.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class OaMeetingRoomTakingDaoImpl extends BaseHibernateDao<OaMeetingRoomTaking,java.lang.Integer>  implements OaMeetingRoomTakingDao<OaMeetingRoomTaking,java.lang.Integer>{

	public Class getEntityClass() {
		return OaMeetingRoomTaking.class;
	}
	
	public Page findPage(OaMeetingRoomTakingQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.room_id roomId,e.meetings,t1.name roomName,t1.branchCompanyId,t1.branchCompanyName,t1.address,DATE_FORMAT(now(),'%Y-%m-%d') nowDate,a.title,a.use_type useType,a.period_type periodType,a.order_date orderDate,DATE_FORMAT(a.order_date,'%Y-%m-%d') orderDateString,a.begin_dt beginDt,DATE_FORMAT(a.begin_dt,'%H:%i') beginDtString,a.end_dt endDt,DATE_FORMAT(a.end_dt,'%H:%i') endDtString,a.chair_account chairAccount,a.order_account orderAccount,a.person,a.status,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  oa_meeting_room_taking a ");
		//关联会议室
		sql.append(" left join ( ");
		sql.append(" select a.id,a.name,a.type,a.branch_company_id branchCompanyId,d.name branchCompanyName,a.address,a.size,a.meetings,cc.meetingsname,a.comment,a.status,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  oa_meeting_room a ");
		sql.append(" left join  (select aa.id,group_concat((bb.name) separator ',') meetingsname from (select a.id,a.meetings from oa_meeting_room a where a.type=2) aa,(select b.id,b.name from oa_meeting_room b where b.type=1 ) bb where FIND_IN_SET(bb.id,aa.meetings)>0 group by aa.id) cc on a.id=cc.id ");			
		sql.append(" left join  system_branch_company d  on a.branch_company_id=d.id ");		
		sql.append(" ) t1 on  a.room_id=t1.id ");
		sql.append(" left join  oa_meeting_room e  on a.room_id=e.id ");
		sql.append(" ) t ");
		List values = new ArrayList();
		
		sql.append(" where 1=1 ");
		
		//values.add(query.getProjectId());
		   if(isNotEmpty(query.getBranchCompanyId())) {
	            sql.append(" and  t.branchCompanyId = ?");
	            values.add(query.getBranchCompanyId());
	        }
		   if(isNotEmpty(query.getRoomId())) {
	            sql.append(" and ( t.roomId = ? or FIND_IN_SET(?,t.meetings)>0 ");
	            sql.append(" or exists (select 1 from oa_meeting_room f where f.id=? and FIND_IN_SET(t.roomId,f.meetings)>0 ) ");
	            sql.append(" ) ");
	            values.add(query.getRoomId());
	            values.add(query.getRoomId());
	            values.add(query.getRoomId());
	            
	        }
		   if(isNotEmpty(query.getOrderDate())) {
			    sql.append(" and  (");
	            sql.append("  (t.periodType=1 and t.orderDate = ? )");
	            sql.append(" or (t.periodType=2 and t.orderDate <= ? )");
	            sql.append(" or (t.periodType=3 and MOD((datediff(t.orderDate,?)),7)=0  and t.orderDate <= ? )");
	            sql.append(" or (t.periodType=4 and MOD((datediff(t.orderDate,?)),14)=0 and t.orderDate <= ? )");
	            sql.append(" ) ");
	            
	           // sql.append(" and  not exists (select 1 from oa_meeting_room_taking_cancel c where c.meeting_date=? and c.task_id=t.id )");
	           // values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	            values.add(query.getOrderDate());
	        }
		   if(isNotEmpty(query.getMeetingName())) {
	            sql.append(" and  t.name like ? ");
	            
	            values.add("%"+query.getMeetingName()+"%");

	        }
		   if(isNotEmpty(query.getOrderAccount())) {
	            sql.append(" and  t.orderAccount = ? ");
	            values.add(query.getOrderAccount());
	        }   
	       
	          if(isNotEmpty(query.getStatus())) {
		            sql.append(" and  t.status = ? ");
		            values.add(query.getStatus());
		        }    
	          if(isNotEmpty(query.getOrderDate())) {     
	             sql.append(" order by convert(DATE_FORMAT(t.beginDt,'%H%i'),SIGNED) asc ");
	          }else sql.append(" order by t.orderDate desc,convert(DATE_FORMAT(t.beginDt,'%H%i'),SIGNED) asc ");
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 

	}
	

}
